b68756162fe7b67c75ed83ccabe47faf582a937b,maddash-server/src/main/java/net/es/maddash/MaDDashGlobals.java,MaDDashGlobals,initDatabase,#String#,259

Before Change


            
            Connection conn = this.dataSource.getConnection();
            try{
                conn.prepareStatement("CREATE TABLE checks (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
                    "checkTemplateId INTEGER NOT NULL, gridName VARCHAR(500) NOT NULL, " +
                    "rowName VARCHAR(500) NOT NULL, colName VARCHAR(500) NOT NULL, checkName " +
                    "VARCHAR(500) NOT NULL, rowOrder INT NOT NULL, colOrder INT NOT " +
                    "NULL, description VARCHAR(2000) NOT NULL, prevCheckTime BIGINT " +
                    "NOT NULL, nextCheckTime BIGINT NOT NULL, checkStatus INTEGER " +
                    "NOT NULL, prevResultCode INTEGER NOT NULL, statusMessage VARCHAR(2000) NOT NULL, " +
                    "resultCount INTEGER NOT NULL, active INTEGER NOT NULL)").execute();
                log.debug("Created table checks");
            }catch(SQLException e){
                if("X0Y32".equals(e.getSQLState())){

After Change


            log.debug("Set database to " + dbname);
            log.debug("JDBC_DRIVER is " + JDBC_DRIVER);
            log.debug("JDBC_URL is " + JDBC_URL);
            Connection conn = this.dataSource.getConnection();
            
            //Create tables
            this.execSQLCreate("CREATE TABLE checks (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
                    "checkTemplateId INTEGER NOT NULL, gridName VARCHAR(500) NOT NULL, " +
                    "rowName VARCHAR(500) NOT NULL, colName VARCHAR(500) NOT NULL, checkName " +
                    "VARCHAR(500) NOT NULL, rowOrder INT NOT NULL, colOrder INT NOT " +
                    "NULL, description VARCHAR(2000) NOT NULL, prevCheckTime BIGINT " +
                    "NOT NULL, nextCheckTime BIGINT NOT NULL, checkStatus INTEGER " +
                    "NOT NULL, prevResultCode INTEGER NOT NULL, statusMessage VARCHAR(2000) NOT NULL, " +
                    "resultCount INTEGER NOT NULL, active INTEGER NOT NULL)", conn);
          this.execSQLCreate("CREATE TABLE checkTemplates (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
                    " checkType VARCHAR(500) NOT NULL, checkParams VARCHAR(2000), checkInterval INTEGER NOT NULL, " +
                    "retryInterval INTEGER NOT NULL, retryAttempts INTEGER NOT NULL, " +
                    "timeout INTEGER NOT NULL)", conn);
          this.execSQLCreate("CREATE TABLE results (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, " +
                    "checkId INTEGER NOT NULL, checkTime BIGINT NOT NULL, returnCode " +
                    "INTEGER NOT NULL, returnMessage VARCHAR(2000) NOT NULL, returnParams VARCHAR(2000), " +
                    "resultCount INTEGER NOT NULL, checkStatus INTEGER NOT NULL)", conn);
            //Create indexes - always rebuilds indexes which can help performance.
            //    checks indexes
            this.execSQLCreate("DROP INDEX checksTemplateId", conn);
            this.execSQLCreate("CREATE INDEX checksTemplateId ON checks(checkTemplateId)", conn);
            this.execSQLCreate("DROP INDEX checksGridName", conn);
            this.execSQLCreate("CREATE INDEX checksGridName ON checks(gridName)", conn);
            this.execSQLCreate("DROP INDEX checksRowName", conn);
            this.execSQLCreate("CREATE INDEX checksRowName ON checks(rowName)", conn);
            this.execSQLCreate("DROP INDEX checksColName", conn);
            this.execSQLCreate("CREATE INDEX checksColName ON checks(colName)", conn);
            this.execSQLCreate("DROP INDEX checksCheckName", conn);
            this.execSQLCreate("CREATE INDEX checksCheckName ON checks(checkName)", conn);
            this.execSQLCreate("DROP INDEX checksActive", conn);
            this.execSQLCreate("CREATE INDEX checksActive ON checks(active)", conn);
            //    results indexes
            this.execSQLCreate("DROP INDEX resultsCheckId", conn);
            this.execSQLCreate("CREATE INDEX resultsCheckId ON results(checkId)", conn);
            this.execSQLCreate("DROP INDEX resultsCheckTime", conn);
            //DESC supposedly helps with MAX
            this.execSQLCreate("CREATE INDEX resultsCheckTime ON results(checkTime DESC)", conn);
            
            conn.close();